{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 清理数据 生成我们最终要使用的数据集\n",
    "\n",
    "## 加载原始数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10866 entries, 0 to 10865\n",
      "Data columns (total 21 columns):\n",
      "id                      10866 non-null int64\n",
      "imdb_id                 10856 non-null object\n",
      "popularity              10866 non-null float64\n",
      "budget                  10866 non-null int64\n",
      "revenue                 10866 non-null int64\n",
      "original_title          10866 non-null object\n",
      "cast                    10790 non-null object\n",
      "homepage                2936 non-null object\n",
      "director                10822 non-null object\n",
      "tagline                 8043 non-null object\n",
      "keywords                9373 non-null object\n",
      "overview                10862 non-null object\n",
      "runtime                 10866 non-null int64\n",
      "genres                  10843 non-null object\n",
      "production_companies    9836 non-null object\n",
      "release_date            10866 non-null object\n",
      "vote_count              10866 non-null int64\n",
      "vote_average            10866 non-null float64\n",
      "release_year            10866 non-null int64\n",
      "budget_adj              10866 non-null float64\n",
      "revenue_adj             10866 non-null float64\n",
      "dtypes: float64(4), int64(6), object(11)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "filename = 'movies.csv'\n",
    "movies_df = pd.read_csv(filename)\n",
    "movies_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "观察数据可以发现，总的纪录有10866条，其中存在缺失的字段有imdb_id，cast演员列表，homepage电影首页的URL，director导演列表，tagline电影的标语，keywords 与电影相关的关键字，overview剧情摘要，genres风格列表，production_companies 制作公司列表\n",
    "根据我们项目中的三个问题\n",
    "问题 1：电影类型是如何随着时间的推移发生变化的？\n",
    "问题 2：Universal Pictures 和 Paramount Pictures 之间的对比情况如何？\n",
    "问题 3：改编电影和原创电影的对比情况如何？(通过keywords变量中的based on novel字段来判断)\n",
    "我们得到我们需要数据字段\n",
    "1.id 标识号\n",
    "2.genres 风格列表(电影类型)\n",
    "3.release_year 发行年份\n",
    "4.production_companies 制作公司\n",
    "5.keywords 与电影相关的关键字\n",
    "6.original_title 电影名称\n",
    "然后我们根据观察数据提出下面问题\n",
    "问题 4：导演对电影的影响\n",
    "然后根据我们的问题，取出需要的字段\n",
    "6.budget_adj 根据通货膨胀调整的预算\n",
    "7.revenue_adj 根据通货膨胀调整的收入\n",
    "8.vote_average 平均评分\n",
    "\n",
    "```\n",
    "## 根据我们需要用到的字段，对数据进行清理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10866 entries, 0 to 10865\n",
      "Data columns (total 6 columns):\n",
      "id                10866 non-null int64\n",
      "original_title    10866 non-null object\n",
      "release_year      10866 non-null int64\n",
      "budget_adj        10866 non-null float64\n",
      "revenue_adj       10866 non-null float64\n",
      "vote_average      10866 non-null float64\n",
      "dtypes: float64(3), int64(2), object(1)\n",
      "memory usage: 509.4+ KB\n"
     ]
    }
   ],
   "source": [
    "#lables = ['id','original_title','release_year','budget_adj','revenue_adj','vote_average']\n",
    "new_movies_df = movies_df[['id','original_title','release_year','budget_adj','revenue_adj','vote_average']]\n",
    "new_movies_df.info()\n",
    "new_movies_df.to_excel('new_movies_df.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### genres风格列表处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# split_genres = movies_df['genres'].str.split('|',expand=True)\n",
    "# split_genres['id'] = movies_df['id']\n",
    "# split_genres['release_year'] = movies_df['release_year']\n",
    "# movies_genres_df = split_genres.melt(\n",
    "# id_vars=['id'],\n",
    "# value_vars=[0, 1, 2, 3, 4], \n",
    "# value_name='genres'\n",
    "# ).drop('variable',axis=1).dropna()\n",
    "# movies_genres_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 26970 entries, 0 to 54325\n",
      "Data columns (total 2 columns):\n",
      "id        26970 non-null int64\n",
      "genres    26970 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 632.1+ KB\n"
     ]
    }
   ],
   "source": [
    "split_genres = movies_df['genres'].str.split('|',expand=True)\n",
    "split_genres['id'] = movies_df['id']\n",
    "merged_df = movies_df.merge(split_genres)\n",
    "movies_genres_df = merged_df.melt(\n",
    "id_vars=['id'],   #要保留的主字\n",
    "value_vars=[0, 1, 2, 3, 4],\n",
    "value_name=\"genres\" #拉长的度量值名称\n",
    ").drop('variable',axis=1).dropna()\n",
    "movies_genres_df.info()\n",
    "movies_genres_df.to_excel('movies_genres_year_df.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### production_companies 制作公司列表处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 23231 entries, 0 to 54043\n",
      "Data columns (total 2 columns):\n",
      "id                      23231 non-null int64\n",
      "production_companies    23231 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 544.5+ KB\n"
     ]
    }
   ],
   "source": [
    "#movies_df['production_companies'][movies_df['production_companies'].isnull().values==True]\n",
    "split_production_companies = movies_df['production_companies'].str.split('|',expand=True)\n",
    "split_production_companies['id'] = movies_df['id']\n",
    "merged_production_companies = movies_df.merge(split_production_companies)\n",
    "movies_production_companies_df = merged_production_companies.melt(\n",
    "id_vars=['id'],\n",
    "value_vars=[0, 1, 2, 3, 4],\n",
    "value_name='production_companies'\n",
    ").drop('variable',axis=1).dropna()\n",
    "movies_production_companies_df.info()\n",
    "movies_production_companies_df.to_excel('movies_production_companies_df.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# new_movies = pd.concat([movies_genres_df,movies_production_companies_df],axis=1)\n",
    "# new_movies.info()\n",
    "# new_movies.to_excel('new_movies.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "### keywords处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 37458 entries, 0 to 54339\n",
      "Data columns (total 2 columns):\n",
      "id          37458 non-null int64\n",
      "keywords    37458 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 877.9+ KB\n"
     ]
    }
   ],
   "source": [
    "split_keywords = movies_df['keywords'].str.split('|',expand=True)\n",
    "split_keywords['id'] = movies_df['id']\n",
    "merged_keywords = movies_df.merge(split_keywords)\n",
    "movies_keywords_df = merged_keywords.melt(\n",
    "id_vars=['id'],\n",
    "value_vars=[0, 1, 2, 3, 4],\n",
    "value_name='keywords'\n",
    ").drop('variable',axis=1).dropna()\n",
    "movies_keywords_df.info()\n",
    "movies_keywords_df.to_excel('movies_keywords_df.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# split_genres = new_movies_df['genres'].str.split('|',expand=True)\n",
    "# split_genres.rename(columns={0:'genres1', 1:'genres2', 2:'genres3',\n",
    "#                                            3:'genres4',4:'genres5'},\n",
    "#                                   inplace = True)\n",
    "\n",
    "# frames = [new_movies_df,split_genres,split_production_companies]\n",
    "# data = pd.concat(frames,axis=1).drop(['production_companies','genres'],axis=1)\n",
    "# new_df = data.melt(\n",
    "# id_vars=['id','genres1','genres2','genres3','genres4','genres5'],   #要保留的主字段\n",
    "# value_name=\"production_companies\" #拉长的度量值名称\n",
    "# ).drop('variable',axis=1).dropna()\n",
    "# new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### director处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 11761 entries, 0 to 53932\n",
      "Data columns (total 2 columns):\n",
      "id          11761 non-null int64\n",
      "director    11761 non-null object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 275.6+ KB\n"
     ]
    }
   ],
   "source": [
    "split_director = movies_df['director'].str.split('|',expand=True)\n",
    "split_director['id'] = movies_df['id']\n",
    "merged_director = movies_df.merge(split_director)\n",
    "movies_director_df = merged_director.melt(\n",
    "id_vars=['id'],\n",
    "value_vars=[0, 1, 2, 3, 4],\n",
    "value_name='director'\n",
    ").drop('variable',axis=1).dropna()\n",
    "movies_director_df.info()\n",
    "movies_director_df.to_excel('movies_director_df.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
